package com.systemdevelopment.mainproject.Model;
import javafx.scene.image.Image;

import javax.swing.table.DefaultTableModel;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.net.URISyntaxException;
import java.net.URL;
import java.sql.*;
//import java.sql.Date;
import java.time.Instant;
import java.time.LocalDate;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.Date;

import static com.systemdevelopment.mainproject.Model.DataBaseManager.conn;


public class BorrowRecord {

	public Book book;
	public LocalDate endTime;
	public boolean isReturned;
	public NormalUser borrower;
	public LocalDate returnTime;
	public LocalDate startTime;

	public Book getBook() {
		return book;
	}

	public void setBook(Book book) {
		this.book = book;
	}

	public BorrowRecord(Book book, LocalDate endTime, boolean isReturned, NormalUser borrower, LocalDate returnTime, LocalDate startTime){
		this.book=book;
		this.endTime=endTime;
		this.isReturned=isReturned;
		this.borrower=borrower;
		this.returnTime=returnTime;
		this.startTime=startTime;
	}

	public static void main(String[] args) throws SQLException, FileNotFoundException, URISyntaxException{

	}
	//借阅图书
	public static void Borrow(String account_num1, String isbn1 , LocalDate end_time1,LocalDate start_time1) throws SQLException{
		/*try{
			Connection conn = DataBaseManager.build_conn() ;
		}catch (SQLException e){
			e.printStackTrace();
		}*/
		Connection conn = DataBaseManager.build_conn() ;
		PreparedStatement preSql;

		String sqlStr = "insert into LENDING(ACCOUNT_NUM,ISBN,START_TIME,END_TIME,RETURN_TIME,IS_RETURNED) values (?,?,?,?,null,?)";

		try {
			preSql = conn.prepareStatement(sqlStr);
			preSql.setString(1, account_num1);
			preSql.setString(2, isbn1);
			preSql.setString(3, start_time1.toString());
			preSql.setString(4,end_time1.toString());
			preSql.setString(5, "未归还");
			int ok = preSql.executeUpdate();
			conn.close();
			ChangeStorage1(isbn1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	//借书修改图书状态
	private static void ChangeStorage1(String isbn1) throws SQLException{

		/*try{
			Connection conn = DataBaseManager.build_conn() ;
		}catch (SQLException e){
			e.printStackTrace();
		}*/
		Connection conn = DataBaseManager.build_conn() ;

		PreparedStatement preSql;

		String sqlStr = "update BOOK set NOW_STORAGE=NOW_STORAGE-1 where ISBN = ?";

		try {
			preSql = conn.prepareStatement(sqlStr);
			preSql.setString(1, isbn1);

			int ok = preSql.executeUpdate();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}


	//还书
	public static void Return(String account_num2, String isbn2) throws SQLException{

		/*try{
			Connection conn = DataBaseManager.build_conn() ;
		}catch (SQLException e){
			e.printStackTrace();
		}*/

		Connection conn = DataBaseManager.build_conn() ;

		PreparedStatement preSql;
		String sqlStr = "select now()";
		ResultSet rs;
		LocalDate date=null;
		try {
			preSql = conn.prepareStatement(sqlStr);
			rs = preSql.executeQuery();
			while(rs.next()) {
				date = LocalDate.parse(rs.getString(1));
			}
			sqlStr = "update LENDING set RETURN_TIME =  ?  where ISBN = ?  and ACCOUNT_NUM = ? and IS_RETURNED = ?";
			preSql = conn.prepareStatement(sqlStr);
			preSql.setString(1, LocalDate.now().toString());
			preSql.setString(2, isbn2);
			preSql.setString(3, "未归还");
			int ok = preSql.executeUpdate();

			sqlStr = "update LENDING set IS_RETURNED = ? where ISBN = ? and ACCOUNT_NUM = ? and IS_RETURNED = ?";
			preSql = conn.prepareStatement(sqlStr);
			preSql.setString(1, "已归还");
			preSql.setString(2, isbn2);
			preSql.setString(3, account_num2);
			preSql.setString(4, "未归还");
			ok = preSql.executeUpdate();

			conn.close();
			ChangeStorage2(isbn2);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	//还书修改图书状态
	private static void ChangeStorage2(String isbn2) throws SQLException {
		/*try{
			Connection conn = DataBaseManager.build_conn() ;
		}catch (SQLException e){
			e.printStackTrace();
		}*/

		Connection conn = DataBaseManager.build_conn() ;
		PreparedStatement preSql;

		String sqlStr = "update BOOK set NOW_STORAGE=NOW_STORAGE+1 where ISBN = ?";

		try {
			preSql = conn.prepareStatement(sqlStr);
			preSql.setString(1,isbn2);

			int ok = preSql.executeUpdate();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}


	//得到所有借阅记录
	public static ArrayList<Record> getAllRecord() throws SQLException{

		ArrayList<Record> records=new ArrayList<>();
		Connection conn = DataBaseManager.build_conn() ;
		Statement sta;
		ResultSet rs;
		String sqlStr = "select * from LENDING ";
		try {
			sta = conn.createStatement();
			rs = sta.executeQuery(sqlStr);
			while (rs.next()) {
				String ISBN = rs.getString(2);
				Book curbook=null;
				HashSet<Book> books=Book.getAllBooksFromDB();
				for(Book book:books){
					if(book.getISBN().equals(ISBN))
					{
						curbook=book;
						break;
					}
				}

				// atZone()方法返回在指定时区从此Instant生成的ZonedDateTime。
				LocalDate startDate = LocalDate.parse(rs.getString(3));
				LocalDate endDate = LocalDate.parse(rs.getString(4));
				LocalDate returnDate =LocalDate.MIN;
				if(rs.getString(5)!=null)
				{
					returnDate = LocalDate.parse(rs.getString(5));
				}
				String is_returned = rs.getString(6);
				String autname=null;
				for(Author mainaut: curbook.getAuthor())
					autname= mainaut.getName();
				String readerid=rs.getString(1);
				Record cur=new Record(curbook.getISBN(),curbook.name, autname, startDate.toString(),returnDate.toString(),endDate.toString(),is_returned,readerid);
				records.add(cur);
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return records;
	}

	//根据用户名找借阅记录
	public static ArrayList<Record> getReaderRecord(String account_num)throws SQLException {
		ArrayList<Record> records=new ArrayList<>();
		Connection conn = DataBaseManager.build_conn() ;
		PreparedStatement preSql;
		ResultSet rs;
		String sqlStr = "select * from LENDING where ACCOUNT_NUM = ?";
		try {
			preSql = conn.prepareStatement(sqlStr);
			preSql.setString(1, account_num);
			rs = preSql.executeQuery();
			while (rs.next()) {
				String ISBN = rs.getString(2);
				Book curbook=null;
				HashSet<Book> books=Book.getAllBooksFromDB();
				for(Book book:books){
					if(book.getISBN().equals(ISBN))
					{
						curbook=book;
						break;
					}
				}

				// atZone()方法返回在指定时区从此Instant生成的ZonedDateTime。
				LocalDate startDate = LocalDate.parse(rs.getString(3));
				LocalDate endDate = LocalDate.parse(rs.getString(4));
				LocalDate returnDate =LocalDate.MIN;
				if(rs.getString(5)!=null)
				{
					returnDate = LocalDate.parse(rs.getString(5));
				}
				String is_returned = rs.getString(6);
				String autname=null;
				for(Author mainaut: curbook.getAuthor())
					autname= mainaut.getName();
				String readerid=rs.getString(1);
				Record cur=new Record(curbook.getISBN(),curbook.name, autname, startDate.toString(),returnDate.toString(),endDate.toString(),is_returned,readerid);
				records.add(cur);
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return records;
	}

	//根据ISBN找借阅记录
	public static void getISBNRecord(DefaultTableModel model,String isbn) throws SQLException{

		Connection conn = DataBaseManager.build_conn() ;
		PreparedStatement preSql;
		ResultSet rs;
		String sqlStr = "select * from LENDING where ISBN = ?";
		try {
			preSql = conn.prepareStatement(sqlStr);
			preSql.setString(1, isbn);
			rs = preSql.executeQuery();
			while (rs.next()) {
				String account_num = rs.getString(1);
				Date start_time = rs.getDate(3);
				Date end_time = rs.getDate(4);
				Date return_time = rs.getDate(5);
				String is_returned = rs.getString(6);
				model.addRow(new Vector<>(Arrays.asList(account_num,isbn,start_time,end_time,return_time,is_returned)));
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
    //新增一条记录
	public static void insertRecord(String account_num,String isbn,Date start_time,Date end_time,Date return_time,String is_returned) throws SQLException {
		Connection conn = null;
		try {
			conn = DataBaseManager.build_conn();
			String sql = "INSERT INTO LIB.LENDING VALUES(?,?,?,?,?,?)";
			PreparedStatement pstmt = conn.prepareStatement(sql);

				pstmt.setString(1, account_num);
				pstmt.setString(2, isbn);
				pstmt.setDate(3, (java.sql.Date)start_time);
				pstmt.setDate(4, (java.sql.Date) end_time);
				pstmt.setDate(5, (java.sql.Date)return_time);
				pstmt.setString(6, is_returned);

				pstmt.executeUpdate();

				conn.commit();
			} catch (SQLException Se) {
			Se.printStackTrace();
			assert conn != null;
			conn.rollback();	//回滚
		}
	}
	//删除一条记录,参数是读者账号和ISBN
	public static void deleteRecord(String account_num,String isbn) throws SQLException{
		Connection conn = DataBaseManager.build_conn() ;

		PreparedStatement preSql;


		String sqlStr = "delete from LENDING where ACCOUNT_NUM = ? and ISBN = ?";
		try {
			preSql = conn.prepareStatement(sqlStr);
			preSql.setString(1, account_num);
			preSql.setString(2, isbn);

			int ok = preSql.executeUpdate();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	//修改借阅记录,此处我写的根据账号和ISBN，可以修改借还时间和状态
	public static void updateRecord(String account_num,String isbn,Date start_time,Date end_time,Date return_time,String is_returned) throws SQLException{
		Connection conn = DataBaseManager.build_conn() ;
		PreparedStatement preSql;

		String sqlStr = "update LENDING set START_TIME = ? , END_TIME = ? , RETURN_TIME = ? where ACCOUNT_NUM = ? and ISBN = ?";
		try {
			preSql = conn.prepareStatement(sqlStr);
			preSql.setString(1, account_num);
			preSql.setString(2,isbn );
			preSql.setDate(3, (java.sql.Date) start_time);
			preSql.setDate(4, (java.sql.Date) end_time);
			preSql.setDate(5, (java.sql.Date) return_time);
			preSql.setString(6, is_returned);
			int ok = preSql.executeUpdate();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}



	public BorrowRecord getRecord(){
		return null;
	}

	public BorrowRecord insertRecord(){
		return null;
	}

	public BorrowRecord updateRecord(){
		return null;
	}
	public BorrowRecord deleteBorrowRecord(){
		return null;
	}

}